package bold.storage;


import bold.storage.component.BaseDao;
import bold.storage.component.Where;
import bold.storage.core.DB;
import bold.storage.core.Record;
import bold.storage.core.RecordList;
import bold.storage.ex.BackException;
import bold.storage.ex.NotBackException;
import bold.storage.splice.SqlKit;
import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.logging.Log4j2Filter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.util.JdbcUtils;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.fastjson.JSON;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.dbcp2.BasicDataSource;
import org.junit.Before;
import org.junit.Test;

import java.io.*;
import java.math.BigDecimal;
import java.sql.*;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.util.*;
import java.util.concurrent.CountDownLatch;

/**
 *
 * @author KaiLun
 * @version 2017/3/2 17:44
 */
@Log4j2
public class MySQL extends BaseDao {
    static final String URL = "jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=utf8" +
            "&serverTimezone=Asia/Shanghai&autoReconnect=true&zeroDateTimeBehavior=convertToNull";
    private static DruidDataSource ds;
    static final String USERNAME = "root";
    static final String PASSWORD = "root";

//    static final String URL = "jdbc:mysql://127.0.0.1:3307/inte?useUnicode=true&characterEncoding=utf8" +
//            "&serverTimezone=Asia/Shanghai&autoReconnect=true&zeroDateTimeBehavior=convertToNull";
//    static final String USERNAME = "root";
//    static final String PASSWORD = "root";
//    private static DruidDataSource ds;
    /*并发测试开始*/
    private static Integer count = 30;
    private CountDownLatch downLatch = new CountDownLatch(count);//大家在同一时刻去干一件事情

    @Before
    public void init() {
        log.info("Before init ========");
        WallConfig wallConfig = new WallConfig();
        wallConfig.setDir("META-INF/druid/wall/mysql");

        Log4j2Filter j2Filter = new Log4j2Filter();
        j2Filter.setStatementExecutableSqlLogEnable(true);
        j2Filter.setResultSetLogEnabled(true);

        StatFilter statFilter = new StatFilter();
        statFilter.setMergeSql(true);
        statFilter.setSlowSqlMillis(5000);

        ds = new DruidDataSource();
        List<Filter> filters = Arrays.asList(statFilter, j2Filter);
        ds.setProxyFilters(filters);

        ds.setUrl(URL);
        ds.setUsername(USERNAME);
        ds.setPassword(PASSWORD);
        /* ds.setDriverClassName("oracle.jdbc.OracleDriver");*/

        ds.setMaxActive(1);
        ds.setMaxWait(60000);
        ds.setMinIdle(1);
        ds.setInitialSize(1);
        //默认 false 当其中某一条sql语句出现错误，事务会回滚之前的所有操作。就相当于不会执行此事务里的任何sql语句。
        //ds.setDefaultAutoCommit(false);
        ds.setTimeBetweenEvictionRunsMillis(3000);
        ds.setMinEvictableIdleTimeMillis(30000);


        ds.setValidationQuery("SELECT 1");
        ds.setValidationQueryTimeout(3000);
        ds.setTestWhileIdle(true);
        ds.setTestOnBorrow(false);
        ds.setTestOnReturn(false);
        ds.setTimeBetweenConnectErrorMillis(1000);


        ds.setMaxOpenPreparedStatements(20);
        ds.setPoolPreparedStatements(true);
        ds.setMaxOpenPreparedStatements(20);
        ds.setRemoveAbandoned(true);//超时回收
        ds.setRemoveAbandonedTimeout(3);//超时回收时间
        ds.setLogAbandoned(true);//超时输出日志

        //一个连接在池中最小的生存时间
        ds.setMinEvictableIdleTimeMillis(1000);
        ds.setMaxEvictableIdleTimeMillis(1000);

        DB db = new DB(ds, false);   //第一次使用类 时会进行 类属性 初始化  再进行 对象内存分配
    }

    @Test
    public void maxConcurrent() throws InterruptedException {
        for (int i = 1; i <= count; i++) {
            new Thread(() -> {
                try {
                    downLatch.await();
                } catch (InterruptedException e) {
                }

                try {
                    int row = DB.update("UPDATE  test  set val =val+1 WHERE id =1");
                    System.out.println(row);
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }).start();

            downLatch.countDown();
        }

        Thread.currentThread().join();
        System.out.println("结束");
    }


    @Test
    public void testConn() throws SQLException, InterruptedException {
        new Thread(()->{
            try {
                RecordList query = DB.query("SELECT 1");
                System.out.println(query.toString());


                Thread.sleep(10000L);


                RecordList query2 = DB.query("SELECT 1");
                System.out.println(query2);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }).start();
        Thread.currentThread().join();
    }

    /**
     * 批量插入数据库
     */
    @Test
    public void addAll1() throws IOException, SQLException, InterruptedException {
        File f = new File("C:\\Users\\李凯伦\\Desktop\\java2.txt");
        InputStreamReader inputSR = new InputStreamReader(new FileInputStream(f), "gbk");
        BufferedReader bR = new BufferedReader(inputSR);

        List<Map<String, Object>> listMap = new ArrayList<>(100000);
        String read;

        //记录不能处理
        StringBuffer errSb = new StringBuffer().append(System.lineSeparator());

        while ((read = bR.readLine()) != null) {
            //文字之中还有逗号
            int commaSize = 0;
            for (char c : read.toCharArray()) {
                if (c == ',') {
                    ++commaSize;
                }
            }

            //不合格的数据
            if (commaSize > 6) {
                errSb.append(read).append(System.lineSeparator());
                continue;
            }


            String[] row = read.replaceAll("\"", "").split(",");
            Map<String, Object> map = new LinkedHashMap<>();   //可优化用list好些 key 没有用
            map.put("OFFER_ID", row[0]);
            map.put("OFFER_NAME", row[1]);
            map.put("OFFER_TYPE", row[2]);
            map.put("OFFER_TYPE_NAME", row[3]);
            map.put("PLAN_STARTDATE", row[4]);
            map.put("PLAN_ENDDATE", row[5]);
            map.put("PROD_LINE_ID", row[6]);
            listMap.add(map);
        }

        log.debug("解析总数-> {}", listMap.size());
        log.error("没有处理的 字符串  {}", errSb.toString());

        bR.close();

        Connection conn = getConnection();
        conn.setAutoCommit(false);
        PreparedStatement ps = conn.prepareStatement(" INSERT INTO  PM_OFFER_TYPE " +
                "(OFFER_ID ,OFFER_NAME,OFFER_TYPE,OFFER_TYPE_NAME,PLAN_STARTDATE,PLAN_ENDDATE,PROD_LINE_ID) " +
                "VALUES(?,?,?,?,to_date(?,'YYYYMMDD'),to_date(?,'YYYYMMDD'),?)");

        int commitNum = 0;
        int listIndex = 0;
        long start = System.currentTimeMillis();
        for (Map<String, Object> map : listMap) {
            Object[] params = map.values().toArray();
            ps.setObject(1, params[0]);
            ps.setObject(2, params[1]);
            ps.setObject(3, params[2]);
            ps.setObject(4, params[3]);
            ps.setObject(5, params[4]);
            ps.setObject(6, params[5]);
            ps.setObject(7, params[6]);
            ps.addBatch();
            listIndex++;
            if (listIndex % 10000 == 0) {

                ps.executeBatch();
                conn.commit();
                ps.clearBatch();
                commitNum++;
            }
        }

        ps.executeBatch();
        conn.commit();
        ps.clearBatch();
        commitNum++;

        log.debug("耗时 {}ms  提交总次数{}", System.currentTimeMillis() - start, commitNum);
        Thread.currentThread().join();
        // 耗时 432ms    提交总次数19 每次 5000
        // 耗时 31396ms  提交总次数10 每次 一万
        // 耗时 41797ms  提交总次数4 每次 3万
    }

    /**
     * 批量插入数据库
     */
    @Test
    public void addAll2() throws IOException, SQLException, InterruptedException {

        Connection conn = getConnection();
        conn.setAutoCommit(false);
        PreparedStatement ps = conn.prepareStatement("INSERT INTO `bold`.`user1`(`id`, `text`) VALUES (?, ?);");

        for (int i = 0; i < 100; i++) {
            ps.setObject(1, i);
            ps.setObject(2, i);
            ps.addBatch();
            if (i % 10 == 0) {
                ps.executeBatch();
//                conn.commit();批量发送即可
                ps.clearBatch();
            }
        }

        ps.executeBatch();
        conn.commit();
        ps.clearBatch();
    }


    /**
     * Db like 查询
     */
    @Test
    public void DbLike() throws SQLException {
        RecordList records2 = DB.query("SELECT * from   `table` WHERE `name` LIKE concat('%',?,'%') ", "373");
    }

    /**
     * 事务
     */

    @Test
    public void txDemo() throws SQLException {
        Connection conn = getConnection();
        conn.setAutoCommit(false);

        try {
            PreparedStatement ps = conn.prepareStatement("UPDATE `table` SET `name` ='很好' WHERE id =1;");
            int r1 = ps.executeUpdate();
            ps.close();

            PreparedStatement ps2 = conn.prepareStatement("UPDATE `table` SET `age` ='很好' WHERE id =2;");
            int r2 = ps2.executeUpdate();
            ps.close();
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
        }

    }

    /**
     * 查询对象
     */
    @Test
    public void queryEntity() throws SQLException {
        Table table = query("table").where(where().eq("id", "1")).entity(Table.class);
        System.out.println(JSON.toJSONString(table));
    }


    @Test
    public void test2() throws SQLException {
        RecordList list = DB.query("select ID,END_TIME from PROMOTION_SUBITEM_DEFINE");
        System.out.println(JSON.toJSONString(list));
    }

    @Test
    public void queryList() throws SQLException {
        List<Object> list = DB.queryList("SELECT age FROM `table` WHERE age >?", 1);
        System.out.println(JSON.toJSONString(list));
    }

    /**
     * ,
     * 每一个 sql 遇到错误 回滚,之前所有操作
     * BackException 逻辑回滚
     * NotBackException 不需要回滚了 不需要提交
     */
    @Test
    public void tx() throws SQLException {
        DB.tx(() -> {
            try {
                int a = DB.update("UPDATE `table` SET `name` ='很好' WHERE id =1");
                if (1 == 2) {
                    //可逻辑回滚
                    throw new BackException();
                }
            } catch (SQLException e) {
                log.error(e.getMessage());
                throw new NotBackException();
            }
        });
    }

    /**
     * Db 情况下 in 查询
     **/
    @Test
    public void query() throws SQLException {
        List<String> ids = Arrays.asList("1503393514510001", "1503393525309002");
        RecordList records = DB.query("select * from `table` where id in(" + SqlKit.concatPlaceholder(ids.size()) + ")"
                , ids.toArray());
        records.println();

        Record record = query("table").record();
        Object ob = record.get("add_time");
    }

    /**
     * field 排序
     */
    @Test
    public void query1() throws SQLException {
        Where where = where();
        // where.and(or().in("age","2","3"));
        where.like("name", "1");

        RecordList records = query("table").where(where).recordList();
        records.println();
    }

    @Test
    public void update() throws SQLException {
        boolean result = model("table")
                .set("name", "我是谁").set("xxxx", "xxxxx")
                .filter().updateBy("id", "1503393514510001");
    }

    @Test
    public void update1() throws SQLException, IOException {
        File file = new File("L:\\public\\bold\\storage\\src\\test\\java\\sql\\123.png");
        if (!file.exists()) {
            System.out.println(1);
            return;
        }
        model("account").set("photo", new FileInputStream(file)).updateByPk("1");
    }

    private Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    private void close(ResultSet rs) throws SQLException {
        if (rs != null) {
            rs.close();
        }
    }

    private void close(PreparedStatement ps) throws SQLException {
        if (ps != null) {
            ps.close();
        }
    }

    private void close(Connection coo) throws SQLException {
        if (coo != null) {
            coo.close();
        }
    }

    /**
     * 单独测试
     */
    @Test
    public void run() {
        try {
            Connection conn = getConnection();
            PreparedStatement ps = conn.prepareStatement("Select add_time from `table` where id =1  FOR UPDATE");
            ResultSet rs = ps.executeQuery();


            PreparedStatement ps1 = conn.prepareStatement("UPDATE `table` SET `name` = '" + new Random().nextInt(1000) + "' WHERE id =1 ");
            int result = ps1.executeUpdate();


            new Thread(() -> {
                try {
                    PreparedStatement ps2 = conn.prepareStatement("UPDATE `table` SET `name` = 'fasdfadf' WHERE id =1 ");
                    int aa = ps2.executeUpdate();
                    System.out.println(aa);
                } catch (SQLException e) {
                    System.out.println(e.getErrorCode());
                }
            }).start();

            //完成后关闭
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }

    @Test
    public void base() throws SQLException {
        Connection conn = DriverManager.getConnection(URL, "root", "root");

        PreparedStatement ps = conn.prepareStatement(" Select * from `table` where id =1  FOR UPDATE");
        PreparedStatement ps1 = conn.prepareStatement("unlock tables ");
        ps.executeQuery();
        ps1.executeQuery();
        ps.close();
        conn.close();
    }

    /**
     * 增
     */
    private int insert(String table, Map<String, Object> map) throws SQLException {
        Connection conn = getConnection();
        PreparedStatement ps;
        String sql = SqlKit.generateInsertSql(table, map);
        Object[] params = map.values().toArray();
        ps = conn.prepareStatement(sql);
        int parameterCount = ps.getParameterMetaData().getParameterCount();
        if (params.length != parameterCount) {
            throw new SQLException("参数个数不对， sql期待参数个数：" + parameterCount + ", 传入个数:" + params.length);
        }
        for (int i = 0; i < parameterCount; i++) {
            ps.setObject(i + 1, params[i]);
        }
        int result = ps.executeUpdate();
        log.debug("插入成功数量 :" + result);
        close(ps);
        close(conn);
        return result;
    }


    /**
     * 改
     */
    private int update(String table, Map<String, Object> map) throws SQLException {
        Connection conn = getConnection();
        PreparedStatement ps;
        ps = conn.prepareStatement("");
        int result = ps.executeUpdate();
        log.debug("插入成功数量 :" + result);
        close(ps);
        close(conn);
        return result;
    }

}
